﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
<System.ComponentModel.DataObjectAttribute(True)> _
Partial Public Class BLL_ManageRole
    Public Sub BLL_ManageRole()
        '
        ' TODO: 在此加入建構函式的程式碼
        ''
    End Sub
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)> _
    Public Function BLL_SelectAllRoles() As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        sql.AppendFormat("SELECT A.RoleID,(Papa.BarText+A.RoleName) as RoleName ")
        sql.AppendFormat("FROM Role A ")
        sql.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByRoleID('',0)) Papa on A.RoleID=Papa.[Value] ")
        sql.AppendFormat("ORDER BY Papa.SN; ")
        Dim DS As DataSet = dat.GetData(sql.ToString(), Nothing, CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If
    End Function
 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)> _
    Public Function BLL_Select(ByVal PapaID As String, ByVal Parent As String) As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim sqlParams As SqlParameter() = New SqlParameter(0) {}

        sqlParams(0) = New SqlParameter("@PapaID", SqlDbType.Int)
        sqlParams(0).Value = PapaID

        sql.AppendFormat("SELECT A.RoleID,A.Division,A.RoleName,A.Parent,A.RoleDescription,A.Active,A.CanEdit,B.RoleName as ParentName ")
        sql.AppendFormat("FROM Role A ")
        sql.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByRoleID('',@PapaID)) Papa on A.RoleID=Papa.[Value] ")
        sql.AppendFormat("inner join Role B on A.Parent=B.RoleID ")
        sql.AppendFormat("WHERE A.Active='Y' ")
        sql.AppendFormat("ORDER BY Papa.SN; ")
        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If
    End Function


    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Insert(ByVal Parent As Integer, ByVal Division As String, ByVal RoleName As String, ByVal RoleDescription As String)
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.AppendFormat("INSERT Role ([RoleName],[Division],[RoleDescription],[Parent],[Active],[CanEdit]) ")
        sql.AppendFormat("SELECT  ")
        sql.AppendFormat("'{0}' ", RoleName)
        sql.AppendFormat(",'{0}' ", Division)
        sql.AppendFormat(",'{0}' ", RoleDescription)
        sql.AppendFormat(",'{0}' ", Parent)
        sql.AppendFormat(",'{0}' ", "Y")
        sql.AppendFormat(",'{0}' ", "Y")

        dat.AccData(sql.ToString)
    End Sub
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Function BLL_Delete(ByVal original_RoleID As Integer) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        Dim sqlParams As SqlParameter() = New SqlParameter(0) {}

        sqlParams(0) = New SqlParameter("@RoleID", SqlDbType.Int)
        sqlParams(0).Value = original_RoleID


        sql.AppendFormat("DELETE Role ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        sql.AppendFormat(";DELETE RoleBrandMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        sql.AppendFormat(";DELETE RoleChannelMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        sql.AppendFormat(";DELETE RoleOrganizationMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        sql.AppendFormat(";DELETE RoleProgramMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        sql.AppendFormat(";DELETE RoleUserMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")
        'sql.AppendFormat("@RoleName,@RoleDescription,@Parent,'Y','Y' ")
        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)> _
    Public Function BLL_Update(ByVal original_RoleID As Integer, ByVal Division As String, ByVal RoleName As String, ByVal RoleDescription As String, ByVal Active As String) As DataTable


        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim sqlParams As SqlParameter() = New SqlParameter(4) {}

        sqlParams(0) = New SqlParameter("@RoleID", SqlDbType.Int)
        sqlParams(0).Value = original_RoleID
        sqlParams(1) = New SqlParameter("@RoleName", SqlDbType.NVarChar, 50)
        sqlParams(1).Value = RoleName
        sqlParams(2) = New SqlParameter("@RoleDescription", SqlDbType.NVarChar, 50)
        sqlParams(2).Value = RoleDescription
        sqlParams(3) = New SqlParameter("@Active", SqlDbType.NChar, 1)
        sqlParams(3).Value = Active
        sqlParams(4) = New SqlParameter("@Division", SqlDbType.NVarChar, 50)
        sqlParams(4).Value = Division


        sql.AppendFormat("UPDATE Role ")
        sql.AppendFormat("SET [RoleName]=@RoleName ")
        sql.AppendFormat(",[Division]=@Division ")
        sql.AppendFormat(",[RoleDescription]=@RoleDescription ")
        sql.AppendFormat(",[Active]=@Active ")
        sql.AppendFormat("WHERE RoleID=@RoleID ")


        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function

End Class
